package com.second.oa.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.second.oa.common.DataBaseUtils;
import com.second.oa.dao.BaseDao;


public abstract class BaseDaoImpl<T> implements BaseDao<T> {

	public int getCount(String sql, Object... params) throws SQLException {
		String s = "select count(1) from (" + sql.toString() + ")";
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		
		try{
			conn = DataBaseUtils.getConnection();
			sta = conn.prepareStatement(s);
			// 绑定参数
			if(params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			res = sta.executeQuery();
			res.next();
			
			return res.getInt(1);
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
	}

	/**
	 * 查询单条数据
	 */
	public T get(String sql, Object... params) throws SQLException {
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		
		T t = null;
		try {
			
			conn = DataBaseUtils.getConnection();
			sta = conn.prepareStatement(sql);
			
			// 绑定参数
			if(params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			
			// 执行查询
			res = sta.executeQuery();
			
			// 将查询结果进行封装
			if(res.next()) {
				t = transform(res);
			}
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
		return t;
	}
	
	/**
	 * 查询列表
	 */
	public List<T> list(String sql, int pn, int pr, Object... params)
			throws SQLException {
		List<T> data = new ArrayList<T>();
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		
		// 计算查询数据库的开始和结束位置
		int start = (pn - 1) * pr + 1; 
		int end = (pn * pr);
		
		// 拼接SQL
		String s = "select * from (select temp.*,rownum rn from (" + sql + ") temp where rownum <= ?) where rn >= ?";
		
		try {
			
			conn = DataBaseUtils.getConnection();
			sta = conn.prepareStatement(s);
						
			if(params != null && params.length > 0) {
			// 绑定参数
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			
			int pi = params == null ? 0 : params.length;
			sta.setInt(pi + 1, start);
			sta.setInt(pi + 2, end);
			
			// 执行查询
			res = sta.executeQuery();
			
			// 将查询结果进行封装
			while(res.next()) {
				data.add(transform(res));
			}
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
		
		return data;
	}
	
	/**
	 * 查询列表
	 */
	public List<T> list(String sql, Object... params)
			throws SQLException {
		List<T> data = new ArrayList<T>();
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		
		try {
			
			conn = DataBaseUtils.getConnection();
			sta = conn.prepareStatement(sql);
						
			if(params != null && params.length > 0) {
			// 绑定参数
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			
			// 执行查询
			res = sta.executeQuery();
			
			// 将查询结果进行封装
			while(res.next()) {
				data.add(transform(res));
			}
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
		
		return data;
	}
	
	/**
	 * 将结果集转换为指定对象
	 * @param res
	 * @return
	 */
	protected abstract T transform(ResultSet res) throws SQLException;
}
